ALTER TABLE document DROP COLUMN id_type;
ALTER TABLE document DROP COLUMN "name";
ALTER TABLE document DROP COLUMN description;
ALTER TABLE document DROP COLUMN need_approval;
ALTER TABLE document DROP COLUMN is_draft;

ALTER TABLE document ADD COLUMN document_name character varying(255);
ALTER TABLE document ADD COLUMN extension character varying(20);
ALTER TABLE document ADD COLUMN description character varying(1000);
ALTER TABLE document ADD COLUMN remark character varying(1000);
ALTER TABLE document ADD COLUMN document_type integer;

DROP FUNCTION sp_document_count(integer, integer, character varying, character varying, character varying, boolean, character varying, integer, boolean, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_document_insert(integer, character varying, character varying, character varying, boolean, character varying, integer, boolean, integer, boolean, integer, integer);
DROP FUNCTION sp_document_selectlist(integer, integer, integer, integer, character varying, character varying, character varying, boolean, character varying, integer, boolean, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_document_update(integer, integer, character varying, character varying, character varying, boolean, character varying, integer, boolean, integer, boolean, integer, integer, boolean, timestamp without time zone);

CREATE OR REPLACE FUNCTION sp_document_insert(
	_code	character varying(20) DEFAULT null,
	_is_template	boolean DEFAULT null,
	_object_type	character varying(50) DEFAULT null,
	_object_id	integer DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_creator	integer DEFAULT null,
	_document_name	character varying(255) DEFAULT null,
	_extension	character varying(20) DEFAULT null,
	_description	character varying(1000) DEFAULT null,
	_remark	character varying(1000) DEFAULT null,
	_document_type	integer DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO document(code,is_template,object_type,object_id,status,notification,creator,updator,deletion_time,document_name,extension,description,remark,document_type)
	VALUES(_code,_is_template,_object_type,_object_id,_status,_notification,_creator,_creator,null,_document_name,_extension,_description,_remark,_document_type);
RETURN currval('document_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_document_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_is_template	boolean DEFAULT null,
	_object_type	character varying(50) DEFAULT null,
	_object_id	integer DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_document_name	character varying(255) DEFAULT null,
	_extension	character varying(20) DEFAULT null,
	_description	character varying(1000) DEFAULT null,
	_remark	character varying(1000) DEFAULT null,
	_document_type	integer DEFAULT null
)
RETURNS SETOF document
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM document WHERE
	 	(_id is null OR id = _id)	
	AND (_code is null OR code = _code)	
	AND (_is_template is null OR is_template = _is_template)	
	AND (_object_type is null OR object_type = _object_type)	
	AND (_object_id is null OR object_id = _object_id)	
	AND (_status is null OR status = _status)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_document_name is null OR document_name = _document_name)	
	AND (_extension is null OR extension = _extension)	
	AND (_description is null OR description = _description)	
	AND (_remark is null OR remark = _remark)	
	AND (_document_type is null OR document_type = _document_type)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_document_count (
	_id	integer DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_is_template	boolean DEFAULT null,
	_object_type	character varying(50) DEFAULT null,
	_object_id	integer DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_document_name	character varying(255) DEFAULT null,
	_extension	character varying(20) DEFAULT null,
	_description	character varying(1000) DEFAULT null,
	_remark	character varying(1000) DEFAULT null,
	_document_type	integer DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM document WHERE
	 	(_id is null OR id = _id)	
	AND (_code is null OR code = _code)	
	AND (_is_template is null OR is_template = _is_template)	
	AND (_object_type is null OR object_type = _object_type)	
	AND (_object_id is null OR object_id = _object_id)	
	AND (_status is null OR status = _status)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_document_name is null OR document_name = _document_name)	
	AND (_extension is null OR extension = _extension)	
	AND (_description is null OR description = _description)	
	AND (_remark is null OR remark = _remark)	
	AND (_document_type is null OR document_type = _document_type);
END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION sp_document_update 
(	_id integer,
	_code character varying(20),
	_is_template boolean,
	_object_type character varying(50),
	_object_id integer,
	_status integer,
	_notification integer,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone,
	_document_name character varying(255),
	_extension character varying(20),
	_description character varying(1000),
	_remark character varying(1000),
	_document_type integer  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE document 
	SET 		code = _code,
		is_template = _is_template,
		object_type = _object_type,
		object_id = _object_id,
		status = _status,
		notification = _notification,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time,
		document_name = _document_name,
		extension = _extension,
		description = _description,
		remark = _remark,
		document_type = _document_type
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;

